
{***********************************************************}
{                                                           }
{      Luxena dbExpress eXtension Components                }
{                                                           }
{      TDBXUpdateSQL editor                                 }
{                                                           }
{      Copyright (c) 2001-2006 Luxena Software Company      }
{      http://www.luxena.com                                }
{      e-mail: support@luxena.com                           }
{                                                           }
{***********************************************************}

unit DBXUpdateSQLForm;

{$I lx.inc}

interface

uses
  Classes, Variants, DB, DBXUpdateSQL, TypInfo, DesignEditors, DesignIntf,
{$IFDEF MSWINDOWS}
  Controls, Forms, ComCtrls, StdCtrls, ExtCtrls;
{$ENDIF}
{$IFDEF LINUX}
  QControls, QForms, QComCtrls, QStdCtrls, QExtCtrls;
{$ENDIF}

type
{$IFDEF D6}
  TDesigner = IDesigner;
{$ELSE}
  TDesigner = IFormDesigner;
{$ENDIF}

const
  InsertSQLIndex   = 0;
  UpdateSQLIndex   = 1;
  DeleteSQLIndex   = 2;
  RefreshSQLIndex  = 3;


type

{ TDBXUpdateSQLForm }

  TDBXUpdateSQLForm = class(TForm)
    PageControl: TPageControl;
    tshOptions: TTabSheet;
    tshSQL: TTabSheet;
    gbxSQLGeneration: TGroupBox;
    btnOK: TButton;
    btnCancel: TButton;
    lblTableName: TLabel;
    lblDataSets: TLabel;
    cmbDataSets: TComboBox;
    lbxUpdateFields: TListBox;
    lbxKeyFields: TListBox;
    lblKeyFields: TLabel;
    lblUpdateFields: TLabel;
    btnGenerateSQL: TButton;
    chkQuoteFieldNames: TCheckBox;
    rgbStatementType: TRadioGroup;
    lblSQLText: TLabel;
    memSQL: TMemo;
    cmbTableName: TComboBox;
    btnDataSetFields: TButton;
    chkQuoteTableName: TCheckBox;
    procedure cmbDataSetsChange(Sender: TObject);
    procedure btnDataSetFieldsClick(Sender: TObject);
    procedure cmbTableNameChange(Sender: TObject);
    procedure btnGenerateSQLClick(Sender: TObject);
    procedure rgbStatementTypeClick(Sender: TObject);
    procedure memSQLChange(Sender: TObject);
    procedure cmbTableNameDropDown(Sender: TObject);
  private
    FUpdateSQL: TDBXUpdateSQL;
    FSelectedDataSet: TDataSet;
    FQuoteStr: string;
    FSQL: array [InsertSQLIndex..RefreshSQLIndex] of TStrings;
    FDesigner: TDesigner;
    procedure AddDataSetName(const S: string);
  public
    constructor CreateEditor(Designer: TDesigner);
    destructor Destroy; override;
    procedure Edit(UpdateSQL: TDBXUpdateSQL);
  end;

  
{ TDBXUpdateSQLEditor }

  TDBXUpdateSQLEditor = class(TComponentEditor)
  public
    procedure ExecuteVerb(Index: Integer); override;
    function GetVerb(Index: Integer): string; override;
    function GetVerbCount: Integer; override;
  end;


implementation

uses SqlExpr;

{$R *.dfm}

const
  SUpdateSQLEditor = '&DBXUpdateSQL Editor...';

  
{ TGenSQLForm }

constructor TDBXUpdateSQLForm.CreateEditor(Designer: TDesigner);
const
  TypeData: TTypeData = (
    ClassType: TDataSet;
    ParentInfo: nil;
    PropCount: 1;
    UnitName: '');
begin
  inherited Create(nil);
  FSQL[InsertSQLIndex] := TStringList.Create;
  FSQL[UpdateSQLIndex] := TStringList.Create;
  FSQL[DeleteSQLIndex] := TStringList.Create;
  FSQL[RefreshSQLIndex] := TStringList.Create;
  FDesigner := Designer;
  FDesigner.GetComponentNames(@TypeData, AddDataSetName);
end;

procedure TDBXUpdateSQLForm.AddDataSetName(const S: string);
begin
  cmbDataSets.Items.Add(S);
end;

destructor TDBXUpdateSQLForm.Destroy;
begin
  FSQL[InsertSQLIndex].Free;
  FSQL[UpdateSQLIndex].Free;
  FSQL[DeleteSQLIndex].Free;
  FSQL[RefreshSQLIndex].Free;
  inherited;
end;

procedure TDBXUpdateSQLForm.cmbTableNameChange(Sender: TObject);
begin
  btnGenerateSQL.Enabled := cmbTableName.Text <> '';
end;

procedure TDBXUpdateSQLForm.cmbDataSetsChange(Sender: TObject);
begin
  if cmbDataSets.ItemIndex > -1 then
    FSelectedDataSet := FDesigner.GetComponent(cmbDataSets.Text) as TDataSet
  else
    FSelectedDataSet := nil;
  btnDatasetFields.Enabled := FSelectedDataSet <> nil;
  if FSelectedDataSet <> nil then
  begin
    cmbTableName.Text := IProviderSupport(FSelectedDataSet).PSGetTableName;
    FQuoteStr := IProviderSupport(FSelectedDataSet).PSGetQuoteChar;
  end else
    cmbTableName.Text := '';
  cmbTableNameChange(cmbTableName);
end;

procedure TDBXUpdateSQLForm.btnDataSetFieldsClick(Sender: TObject);
var
  I: Integer;
  FieldDefs: TFieldDefs;
  FieldDef: TFieldDef;
  KeyFieldNames: TStringList;
  IsKeyField: Boolean;
begin
  FieldDefs := FSelectedDataSet.FieldDefs;
  FieldDefs.Update;
  lbxKeyFields.Items.BeginUpdate;
  lbxUpdateFields.Items.BeginUpdate;
  try
    lbxKeyFields.Clear;
    lbxUpdateFields.Clear;
    KeyFieldNames := TStringList.Create;
    try
      KeyFieldNames.CommaText := IProviderSupport(FSelectedDataSet).PSGetKeyFields;
      for I := 0 to FieldDefs.Count - 1 do
      begin
        FieldDef := FieldDefs[I];
        IsKeyField := KeyFieldNames.IndexOf(FieldDef.Name) >= 0;
        with lbxKeyFields do
          Selected[Items.Add(FieldDef.Name)] := IsKeyField;
        with lbxUpdateFields do
          Selected[Items.Add(FieldDef.Name)] := not IsKeyField;
      end;
    finally
      KeyFieldNames.Free;
    end;
  finally
    lbxKeyFields.Items.EndUpdate;
    lbxUpdateFields.Items.EndUpdate;
  end;
end;

procedure TDBXUpdateSQLForm.btnGenerateSQLClick(Sender: TObject);
var
  TableName: string;
  I: Integer;
  FieldNames: TStrings;
  FieldName: string;
  UpdateParam, WhereParam: string;
  InsertFields, InsertParams, InsertSeparator: string;
  SetSQL, SetSQLSeparator: string;
  SelectFields, SelectSeparator: string;
  ModifyWhereSQL, RefreshWhereSQL, WhereSQLSeparator: string;
begin
  TableName := cmbTableName.Text;
  if chkQuoteTableName.Checked then
    TableName := FQuoteStr + TableName + FQuoteStr;

  FieldNames := lbxKeyFields.Items;
  for I := 0 to FieldNames.Count - 1 do
  begin
    FieldName := FieldNames[I];
    if chkQuoteFieldNames.Checked then
    begin
      UpdateParam := ':"' + FieldName + '"';
      WhereParam := ':"OLD_' + FieldName + '"';
      FieldName := FQuoteStr + FieldName + FQuoteStr;
    end else
    begin
      UpdateParam := ':' + FieldName;
      WhereParam := ':OLD_' + FieldName;
    end;
    if lbxKeyFields.Selected[I] or lbxUpdateFields.Selected[I] then
    begin
      InsertFields := InsertFields + InsertSeparator + FieldName;
      InsertParams := InsertParams + InsertSeparator + UpdateParam;
      InsertSeparator := ', ';
      if lbxUpdateFields.Selected[I] then
      begin
        SetSQL := SetSQL + SetSQLSeparator + FieldName + ' = ' + UpdateParam;
        SetSQLSeparator := ',' + SLineBreak + '  ';
        SelectFields := SelectFields + SelectSeparator + FieldName;
        SelectSeparator := ', ';
      end;
      if lbxKeyFields.Selected[I] then
      begin
        ModifyWhereSQL := ModifyWhereSQL + WhereSQLSeparator + FieldName + ' = ' + WhereParam;
        RefreshWhereSQL := RefreshWhereSQL + WhereSQLSeparator + FieldName + ' = ' + UpdateParam;
        WhereSQLSeparator := SLineBreak + '  AND ';
      end;
    end;
  end;
  FSQL[InsertSQLIndex].Text := 'INSERT INTO ' + TableName + SLineBreak +
    '  (' + InsertFields + ')' + SLineBreak + 'VALUES' + SLineBreak +
    '  (' + InsertParams + ')';
  ModifyWhereSQL := SLineBreak + 'WHERE' + SLineBreak + '  ' + ModifyWhereSQL;
  FSQL[UpdateSQLIndex].Text := 'UPDATE ' + TableName + SLineBreak + 'SET' +
    SLineBreak + '  ' + SetSQL + ModifyWhereSQL;
  FSQL[DeleteSQLIndex].Text := 'DELETE FROM ' + TableName + ModifyWhereSQL;
  RefreshWhereSQL := SLineBreak + 'WHERE' + SLineBreak + '  ' + RefreshWhereSQL;
  FSQL[RefreshSQLIndex].Text := 'SELECT ' + SelectFields + SLineBreak +
   'FROM ' + TableName + RefreshWhereSQL;
  PageControl.ActivePage := tshSQL;
end;

procedure TDBXUpdateSQLForm.rgbStatementTypeClick(Sender: TObject);
begin
  memSQL.OnChange := nil;
  memSQL.Lines.Assign(FSQL[rgbStatementType.ItemIndex]);
  memSQL.OnChange := memSQLChange;
end;

procedure TDBXUpdateSQLForm.Edit(UpdateSQL: TDBXUpdateSQL);
begin
  FUpdateSQL := UpdateSQL;
  if UpdateSQL.Owner <> nil then
    Caption := UpdateSQL.Owner.Name + '.' + UpdateSQL.Name
  else
    Caption := UpdateSQL.Name;
  if UpdateSQL.DataSet <> nil then
  begin
    cmbDataSets.ItemIndex := cmbDataSets.Items.IndexOf(UpdateSQL.DataSet.Name);
    cmbDataSetsChange(cmbDataSets);
  end;
  FSQL[InsertSQLIndex].Assign(UpdateSQL.InsertSQL);
  FSQL[UpdateSQLIndex].Assign(UpdateSQL.ModifySQL);
  FSQL[DeleteSQLIndex].Assign(UpdateSQL.DeleteSQL);
  FSQL[RefreshSQLIndex].Assign(UpdateSQL.RefreshSQL);
  if ShowModal = mrOK then
  begin
    FDesigner.Modified;
    UpdateSQL.InsertSQL.Assign(FSQL[InsertSQLIndex]);
    UpdateSQL.ModifySQL.Assign(FSQL[UpdateSQLIndex]);
    UpdateSQL.DeleteSQL.Assign(FSQL[DeleteSQLIndex]);
    UpdateSQL.RefreshSQL.Assign(FSQL[RefreshSQLIndex]);
  end;
end;

procedure TDBXUpdateSQLForm.memSQLChange(Sender: TObject);
begin
  FSQL[rgbStatementType.ItemIndex].Assign(memSQL.Lines);
end;


{ TDBXUpdateSQLEditor }

procedure TDBXUpdateSQLEditor.ExecuteVerb(Index: Integer);
begin
  if Index = 0 then
     with TDBXUpdateSQLForm.CreateEditor(Designer) do
       try
         Edit(Component as TDBXUpdateSQL);
       finally
         Free;
       end;
end;

function TDBXUpdateSQLEditor.GetVerb(Index: Integer): string;
begin
  Result := SUpdateSQLEditor;
end;

function TDBXUpdateSQLEditor.GetVerbCount: Integer;
begin
  Result := 1;
end;

procedure TDBXUpdateSQLForm.cmbTableNameDropDown(Sender: TObject);
var
  DataSet: TCustomSQLDataSet;
begin
  DataSet := TCustomSQLDataSet(FUpdateSQL.DataSet);
  if (DataSet <> nil) and (DataSet.SQLConnection <> nil) then
    DataSet.SQLConnection.GetTableNames(cmbTableName.Items);
end;


end.